#!/usr/bin/python
# -*- coding: UTF-8 -*-
# author:heqiang

import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Boolean, TIMESTAMP, and_, desc
from sqlalchemy.orm import sessionmaker
import datetime
from LoggerBase import log

# 数据库类型
engine = sqlalchemy.create_engine('sqlite:////sqlite.db')

base = declarative_base()

Session = sessionmaker(bind=engine)
session = Session()


class TklRecord(base):
    # 指定本类映射到TklRecord表
    __tablename__ = 'TklRecord'

    id = Column(Integer, primary_key=True, autoincrement=True)
    # 指定name映射到name字段; name字段为字符串类形，
    username = Column(String(32))
    wx_id = Column(String(32))
    createtime = Column(TIMESTAMP)
    taotitle = Column(String(128))
    tkl = Column(String(128))
    tao_url = Column(String(128))
    tao_id = Column(String(32))
    fee = Column(Integer)
    count = Column(String(1024))  # 关联的所有订单


class UserRecord(base):
    # 指定本类映射到TklRecord表
    __tablename__ = 'UserRecord'

    # id = Column(Integer, primary_key=True, autoincrement=True)
    # 指定name映射到name字段; name字段为字符串类形，
    username = Column(String(32))
    wx_id = Column(String(32), primary_key=True)
    total_fee = Column(Integer, default=0)
    createtime = Column(TIMESTAMP)


class OrderRecord(base):
    # 指定本类映射到OrderRecord表
    __tablename__ = 'OrderRecord'

    tao_id = Column(String(32))
    trade_id = Column(String(32), primary_key=True)
    ordertype = Column(String(32))
    paytime = Column(TIMESTAMP)
    donetime = Column(TIMESTAMP)
    fee = Column(Integer)
    tklfee = Column(Integer)
    wx_id = Column(String(32))
    flag = Column(Boolean)  # true 无法更改wx_id
    synced = Column(Boolean)  # True,已同步fee
    parent_id = Column(String(32))


class GetFeeRecord(base):
    # 指定本类映射到TklRecord表
    __tablename__ = 'GetFeeRecord'

    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(32))
    wx_id = Column(String(32))
    fee = Column(Integer, default=0)
    createtime = Column(TIMESTAMP)


def create_table():
    base.metadata.create_all(engine, checkfirst=True)


def session_commit():
    try:
        session.commit()
    except:
        session.rollback()


def add_user(username, wx_id, total_fee=0):
    now_time = datetime.datetime.now()
    new_user = UserRecord(username=username, wx_id=wx_id, total_fee=total_fee, createtime=now_time)
    session.add(new_user)
    session_commit()


def update_user(wx_id, alipay_id):
    # 弃用
    session.query(UserRecord).filter_by(wx_id=wx_id).update({UserRecord.alipay_id: alipay_id})
    session_commit()


def del_user(wx_id):
    # 要删除需要先将记录查出来
    del_user = session.query(UserRecord).filter_by(wx_id=wx_id).first()
    # 将ed用户记录删除
    session.delete(del_user)

    # 确认删除
    session_commit()


def add_tkl_record(username, wx_id, re_time, taotitle, tkl, tao_url, tao_id, fee, count=""):
    if isinstance(re_time, str):
        re_time = datetime.datetime.strptime(re_time, '%Y-%m-%d %H:%M:%S')
    add_tkl = TklRecord(username=username, wx_id=wx_id, createtime=re_time, taotitle=taotitle, tkl=tkl, tao_url=tao_url,
                        tao_id=tao_id,
                        fee=fee, count=count)
    session.add(add_tkl)
    session_commit()


def get_tkl_record(tao_id, createtime, distance=15):
    """
    对应时间段和tao_id的tkl
    """
    if isinstance(createtime, str):
        createtime = datetime.datetime.strptime(createtime, '%Y-%m-%d %H:%M:%S')

    createtime2 = createtime - datetime.timedelta(minutes=distance)

    tkls = session.query(TklRecord).filter(
        and_(TklRecord.tao_id == tao_id, TklRecord.createtime <= createtime,
             TklRecord.createtime >= createtime2)).order_by(desc(TklRecord.createtime)).all()
    if len(tkls) == 0:
        plus_distance = distance * 2
        # print(plus_distance)
        if plus_distance > 7200:
            return False
        tkl = get_tkl_record(tao_id, createtime, plus_distance)
        return tkl
    else:
        tkl = tkls[0]
        return [tkl.id, tkl.username, tkl.wx_id, tkl.fee, tkl.tao_id, tkl.count, tkl.taotitle]


def update_tkl_count(id, tradeid):
    tradeid = tradeid + ";"
    tkl = session.query(TklRecord).filter_by(id=id).first()
    count = tkl.count
    count = count + tradeid
    tkl.count = count
    # session.query(TklRecord).filter_by(id=id).update({TklRecord.count: count})
    session_commit()


def get_user_info(wx_id):
    userinfo = session.query(UserRecord).filter_by(wx_id=wx_id).first()
    return [userinfo.total_fee]


def check_user(wx_id):
    user = session.query(UserRecord).filter_by(wx_id=wx_id).first()
    return [user.username, user.wx_id, user.total_fee, user.createtime]


def add_user_fee(wx_id, fee):
    mod_user = session.query(UserRecord).filter_by(wx_id=wx_id).first()
    total = mod_user.total_fee
    total += fee
    mod_user.total_fee = total
    session_commit()


def reset_user_fee(wx_id, fee=0):
    session.query(UserRecord).filter_by(wx_id=wx_id).update({UserRecord.total_fee: fee})
    session_commit()


def get_user_nodone_fee(wx_id):
    orders = session.query(OrderRecord).filter(
        and_(OrderRecord.wx_id == wx_id, OrderRecord.synced != True, OrderRecord.ordertype != "13")).all()
    nodone_fee = 0
    for order in orders:
        # print(type(order.ordertype))
        # print(order.ordertype)
        nodone_fee += order.tklfee
    return nodone_fee


def get_user_orders(wx_id):
    orders = session.query(OrderRecord).filter(
        and_(OrderRecord.wx_id == wx_id, OrderRecord.synced != True)).all()
    message = ""
    for order in orders:
        message += order.trade_id
        message += "\n"
    return message


def add_order(tao_id, trade_id, ordertype, paytime, donetime, fee, tklfee, wx_id, trade_parent_id, flag=False,
              synced=False):
    if isinstance(paytime, str):
        paytime = datetime.datetime.strptime(paytime, '%Y-%m-%d %H:%M:%S')
    if isinstance(donetime, str):
        donetime = datetime.datetime.strptime(paytime, '%Y-%m-%d %H:%M:%S')
    print("add trade id %s" % trade_id)
    addorder = OrderRecord(tao_id=tao_id, trade_id=trade_id, ordertype=ordertype, paytime=paytime,
                           donetime=donetime, fee=fee, tklfee=tklfee,
                           wx_id=wx_id, flag=flag, synced=synced, parent_id=trade_parent_id)
    session.add(addorder)
    session_commit()


def update_oder_flag(trade_id, tk_earning_time, flag, synced, wx_id=""):
    if wx_id:
        session.query(OrderRecord).filter_by(trade_id=trade_id).update(
            {OrderRecord.donetime: tk_earning_time, OrderRecord.flag: flag, OrderRecord.synced: synced,
             OrderRecord.wx_id: wx_id})
    else:
        session.query(OrderRecord).filter_by(trade_id=trade_id).update(
            {OrderRecord.donetime: tk_earning_time, OrderRecord.flag: flag, OrderRecord.synced: synced})
    session_commit()


def update_oder_wx(trade_id, tklfee, wx_id):
    session.query(OrderRecord).filter_by(trade_id=trade_id).update(
        {OrderRecord.tklfee: tklfee, OrderRecord.wx_id: wx_id})
    session_commit()


def update_oder_type(trade_id, ordertype):
    session.query(OrderRecord).filter_by(trade_id=trade_id).update(
        {OrderRecord.ordertype: ordertype})
    session_commit()


def get_order(trade_id):
    try:
        order = session.query(OrderRecord).filter_by(trade_id=trade_id).first()
        return [order.wx_id, order.tklfee, order.flag, order.synced, order.donetime]
    except:
        return False


def get_trade_id_by_parent(parent_trade_id):
    try:
        orders = session.query(OrderRecord).filter_by(parent_id=parent_trade_id).all()
        trade_id_list = []
        for order in orders:
            trade_id_list.append(order.trade_id)
        if trade_id_list == []:
            return False
        return trade_id_list
    except Exception as e:
        print(e)
        return False


def del_order_half_year(timestamp):
    """
    timestamp 时间类型
    """
    timestamp_half_year = timestamp - datetime.timedelta(weeks=12)  # 改为三个月
    session.query(TklRecord).filter(
        and_(TklRecord.createtime <= timestamp_half_year)).delete()
    session_commit()

    session.query(OrderRecord).filter(
        and_(OrderRecord.donetime <= timestamp_half_year)).delete()
    # 确认删除
    session_commit()


def get_all_fee():
    orders = session.query(UserRecord).filter(
        and_(UserRecord.total_fee != 0, )).all()
    nodone_fee = 0
    for order in orders:
        # print(type(order.ordertype))
        # print(order.ordertype)
        nodone_fee += order.total_fee
    return nodone_fee


if __name__ == "__main__":
    pass
    # create_table()
    # now_time = datetime.datetime.now()
    # now_time = now_time.strftime('%Y-%m-%d %H:%M:%S')
    # add_tkl_record("username", "wx_id", now_time, "tkl-edwd2f232323f2445@#$^&*(", "tao_id", 1.2)
    # add_user(username="test1",wx_id="2")
    # add_user(username="user234",wx_id="32")
    # add_user(username="test3",wx_id="5")
    # add_user_fanli ("2", 2)
    # reset_user_fanli("2")
    # a = check_user(wx_id="2")
    # add_order(tao_id="608054988202",trade_id="341324312",fanli="0.5",createtime="2022-02-22 16:44:46")
    # print(a)
    # fee = get_tkl_record(tao_id="608054988202", createtime="2022-02-22 16:14:46")
    # print(fee)
    # add_user("ferw4354234","13424141r4134112")
    # del_user(wx_id="oxVH76sARK3kcWQMvS8Zpr0kE6gY")
    # aaa = get_user_nodone_fee(wx_id="oxVH76sARK3kcWQMvS8Zpr0kE6gY")
    # print(aaa)
    # start_time = "2022-06-07 17:06:00"
    # start_time_day = datetime.datetime.strptime(start_time, "%Y-%m-%d %H:%M:%S")
    # aaa = del_order_half_year(start_time_day)
    # result = get_user_nodone_fee(wx_id="oxVH76sARK3kcWQMvS8Zpr0kE6gY")
    # result = get_trade_id_by_parent(parent_trade_id="123456")
    result = get_all_fee()
    print(result)
    # oxVH76k26791MzZWe-SOaGG3mN_c
    # 已充 2232.63
    # hhhh = round(float("0.10") * 0.9, 2)
    # print(hhhh)

